﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports QuanLyNhaSachDTO
Public Class ThongKeSachDAO

#Region "Duyệt Danh Sách"

    Public Shared Function DaTatableToListof(ByVal Table As DataTable) As List(Of ThongKeSach)
        Dim arr As New List(Of ThongKeSach)
        For Each row As DataRow In Table.Rows
            Dim item As New ThongKeSach
            item.IDSach = row("IDSach")
            item.Nam = row("Nam")
            item.PhatSinh = row("PhatSinh")
            item.Thang = row("Thang")
            item.TonCuoi = row("TonCuoi")
            item.TonDau = row("TonDau")
            arr.Add(item)
        Next
        Return arr
    End Function

    Public Function LayDanhSach() As List(Of ThongKeSach)
        Dim arr As New List(Of ThongKeSach)
        Dim da As New DataTable
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "select * form ThongKeSach"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim adr As New OleDbDataAdapter()
        adr.SelectCommand = cmd
        adr.Fill(da)
        arr = DaTatableToListof(da)
        cn.Close()
        Return arr
    End Function

    Public Function LayDanhSach(ByVal nam As Integer) As List(Of ThongKeSach)
        Dim arr As New List(Of ThongKeSach)
        Dim da As New DataTable
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "select * form ThongKeSach where Nam = ?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters("@Nam").Value = nam
        Dim adr As New OleDbDataAdapter()
        adr.SelectCommand = cmd
        adr.Fill(da)
        arr = DaTatableToListof(da)
        cn.Close()
        Return arr
    End Function
#End Region

#Region "Update Danh Sách"
    Public Function ThemThongKeSach(ByVal reportbook As ThongKeSach) As Boolean
        Dim kq As Boolean = False
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "insert into ThongKeSach(Thang,Nam,IDSach,TonDau,PhatSinh,TonCuoi) values(?,?,?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@Thang", OleDbType.Integer)
        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters.Add("@IDSach", OleDbType.Integer)
        cmd.Parameters.Add("@TonDau", OleDbType.Integer)
        cmd.Parameters.Add("@PhatSinh", OleDbType.WChar)
        cmd.Parameters.Add("@TonCuoi", OleDbType.Integer)

        cmd.Parameters("@Thang").Value = reportbook.Thang
        cmd.Parameters("@Nam").Value = reportbook.Nam
        cmd.Parameters("@IDSach").Value = reportbook.IDSach
        cmd.Parameters("@TonDau").Value = reportbook.TonDau
        cmd.Parameters("@PhatSinh").Value = reportbook.PhatSinh
        cmd.Parameters("@TonCuoi").Value = reportbook.TonCuoi

        If cmd.ExecuteNonQuery() <> 0 Then
            kq = True
        End If
        cn.Close()
        Return kq
    End Function

    Public Function XoaThongKeSach(ByVal reporbook As ThongKeSach) As Exception
        Dim arrex As Exception
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "delete from ThongKeSach where Nam = ? and Thang = ? IDSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters.Add("@Thang", OleDbType.Integer)
        cmd.Parameters.Add("@IDSach", OleDbType.Integer)

        cmd.Parameters("@Nam").Value = reporbook.Nam
        cmd.Parameters("@Thang").Value = reporbook.Thang
        cmd.Parameters("@IDSach").Value = reporbook.IDSach

        If cmd.ExecuteNonQuery() <> 0 Then
            arrex = New Exception("Xóa Thành Công IDSach: " & reporbook.IDSach & " Tháng " & reporbook.Thang & " Năm " & reporbook.Nam)
        Else
            arrex = New Exception("Xóa Thất Bại IDSach: " & reporbook.IDSach & " Tháng " & reporbook.Thang & " Năm " & reporbook.Nam)
        End If
        cn.Close()
        Return arrex
    End Function

    Public Function XoaThongKeSach(ByVal reporbooks As List(Of ThongKeSach)) As List(Of Exception)
        Dim arrex As New List(Of Exception)
        For Each sachs As ThongKeSach In reporbooks
            Dim ex As Exception = XoaThongKeSach(sachs)
            arrex.Add(ex)
        Next
        Return arrex
    End Function

    Public Function XoaThongKeSach(ByVal Nam As Integer) As Exception
        Dim arrex As New Exception
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "delete from ThongKeSach where Nam = ?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters("@Nam").Value = Nam
        If cmd.ExecuteNonQuery() <> 0 Then
            arrex = New Exception("Thống Kê Sách Xóa Thành Công (Nam: " & Nam & ")")
        Else
            arrex = New Exception("Thống Kê Sách Xóa Thất Bại (Nam: " & Nam & ")")
        End If
        cn.Close()
        Return arrex
    End Function

    Public Function XoaThongKeSach(ByVal Nam As Integer, ByVal Thang As Integer) As Exception
        Dim ex As New Exception()
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "delete from ThongKeSach where Nam = ? and Thang = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters.Add("@Thang", OleDbType.Integer)

        cmd.Parameters("@Nam").Value = Nam
        cmd.Parameters("@Thang").Value = Thang

        If cmd.ExecuteNonQuery() <> 0 Then
            ex = New Exception("Thống Kê Sách Xóa Thành Công (Nam: " & Nam & " Thang: " & Thang & ")")
        Else
            ex = New Exception("Thống Kê Sách Xóa Thất Bại (Nam: " & Nam & " Thang: " & Thang & ")")
        End If
        cn.Close()
        Return ex
    End Function

    Public Function XoaThongKeSachTheoID(ByVal IDSach As Integer) As Exception
        Dim ex As New Exception
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "delete from ThongKeSach where IDSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@IDSach", OleDbType.Integer)
        cmd.Parameters("@IDSach").Value = IDSach

        If cmd.ExecuteNonQuery() <> 0 Then
            ex = New Exception("Thống Kê Sách Xóa Thành Công (IDSach: " & IDSach & ")")
        Else
            ex = New Exception("Thống Kê Sách Xóa Thất Bại (IDSach: " & IDSach & ")")
        End If
        cn.Close()
        Return ex
    End Function

    Public Function SuaThongKeSach(ByVal reporbook As ThongKeSach) As Exception
        Dim ex As New Exception()
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "update ThongKeSach set TonDau = ? and PhatSinh = ? and TonCuoi = ? where Nam = ? and Thang = ? and IDSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@TonDau", OleDbType.Integer)
        cmd.Parameters.Add("@PhatSinh", OleDbType.WChar)
        cmd.Parameters.Add("@TonCuoi", OleDbType.Integer)
        cmd.Parameters.Add("@Nam", OleDbType.Integer)
        cmd.Parameters.Add("@Thang", OleDbType.Integer)
        cmd.Parameters.Add("@IDSach", OleDbType.Integer)

        cmd.Parameters("@TonDau").Value = reporbook.TonDau
        cmd.Parameters("@PhatSinh").Value = reporbook.PhatSinh
        cmd.Parameters("@TonCuoi").Value = reporbook.TonCuoi
        cmd.Parameters("@Nam").Value = reporbook.Nam
        cmd.Parameters("@Thang").Value = reporbook.Thang
        cmd.Parameters("@IDSach").Value = reporbook.IDSach

        If cmd.ExecuteNonQuery() <> 0 Then
            ex = New Exception("Thống Kê Sách Sửa Thành Công (Nam: " & reporbook.Nam & " Thang: " & reporbook.Thang & "IDSach: " & reporbook.IDSach & ")")
        Else
            ex = New Exception("Thống Kê Sách Sửa Thất Bại (Nam: " & reporbook.Nam & " Thang: " & reporbook.Thang & "IDSach: " & reporbook.IDSach & ")")
        End If
        cn.Close()
        Return ex
    End Function
#End Region
End Class
